import calendar
import copy
import datetime
import os
import sys

# 将工程目录加入包扫描
currPath = os.path.abspath(os.path.dirname(__file__))
srcPath = os.path.split(currPath)[0]
rootPath = os.path.split(srcPath)[0]
projectPath = os.path.split(rootPath)[0]
sys.path.append(projectPath)

from typing import List
from src.tasks.enterpriseToEs.EntEnterprise import EntEnterprise
from src.utils import CommonUtils
from src.db.FCDB import FCDB
from src.db.CcxDataCenterDb import CcxDataCenter
from src.db.LocalDb import LocalDb


class SyncEnterpriseNameList:
    def __init__(self, logger, localDb,dataCenterDb, fcdb, useCache=False):
        self.logger = logger
        self.localDb = localDb
        self.fcdb = fcdb
        self.useCache = useCache
        self.dataCenterDb = dataCenterDb

    # 抽取最新主体名单
    def __queryRemote__(self):
        cacheFile = "__queryRemote__.tmp"
        if self.useCache:
            # 读取缓存
            tmp = CommonUtils.load_dump(cacheFile)
            if tmp is not None:
                self.logger.info("Load cache")
                return tmp
        sql = """
            SELECT * FROM (
            SELECT COMPCODE, COMPNAME, OUTCODE, DATA_DOWNLOAD_TIME, ROW_NUMBER() OVER( PARTITION BY COMPCODE ORDER BY CTYPE ) AS NO FROM 
              tq_comp_codecor 
              WHERE ISVALID = 1 AND 
              COMPCODE IN (
                  SELECT  DISTINCT IFNULL(b.COMPCODE, a.ISSUECOMPCODE) AS COMPCODE  FROM  tq_bd_newestbasicinfo  a
                  LEFT JOIN tq_bd_bonddebt b ON a.SECURITYID = b.SECURITYID AND b.ISVALID = 1
                  WHERE a.BONDTYPE1 IN ('5','6') AND a.BONDTYPE2 NOT IN('511','512') AND a.ISVALID=1
                  ) 
            ) t  WHERE t.no = 1
         """
        tmp = self.fcdb.Query(sql)
        if self.useCache:
            CommonUtils.save_dump(cacheFile, tmp)
        return tmp

    def __queryLocal__(self):
        sql = """
        select a.id,a.comp_code,a.enterprise_name,a.credit_code,a.create_time,a.update_time,b.level as region_level from ent_enterprise a LEFT JOIN   `data-center`.regions b ON a.region_code = b.code where a.comp_code is not null 
        """
        return self.localDb.Query(sql)

    def __queryDataT002ForEs__(self):
        sql = """
        with tmp as (
        select * 
        from regions 
        WHERE ver=0)
        select a.*,b.level as region_level, b.province_code, b.city_code, b.county_code, b.p_functional_code, b.functional_code from ent_enterprise a LEFT JOIN   tmp b ON a.platform_region_code = b.code where 
        a.ent_type = 'T002' 
        """
        return self.localDb.Query(sql)

    def __queryDataOtherForEs__(self):
        sql = """
        with tmp as (
        select * 
        from regions 
        WHERE ver=0)
        select a.*,b.level as region_level, b.province_code, b.city_code, b.county_code, b.p_functional_code, b.functional_code from ent_enterprise a LEFT JOIN   tmp b ON a.region_code = b.code where 
         a.ent_type <> 'T002' 
        """
        return self.localDb.Query(sql)

    def __queryReportT001DataByYear__(self,entEnterprise):
        ent_list = []
        year = calendar.datetime.date.today().year
        start_year = year - 4;

        sql = """
                SELECT index_code , index_value, year FROM fin_index WHERE credit_code = %s and (year between %s and %s) and report_type = 3 and index_code in ('ZB_0101','ZB_0105','ZB_0302','ZB_0303','ZB_0108','ZB_0116','ZB_0110','ZB_0117','ZB_0307','ZB_0311','ZB_0313','ZB_0401')  
            """
        param_list = []
        param_list.append(entEnterprise['credit_code'])
        param_list.append(start_year)
        param_list.append(year)
        list = self.localDb.Query(sql,param_list)
        for i in range(start_year, year):
            tmpEnt = copy.copy(entEnterprise)
            tmpEnt['year'] = i
            if len(list) > 0:
                for j in list:
                    if j['year'] == i:
                        if j['index_value'] is not None and len(str(j['index_value'])) > 0:
                            tmpEnt.update({j['index_code']: float(j['index_value']) / 100000000})
                            if j['index_code'] == 'ZB_0110' or j['index_code'] == 'ZB_0116' or j['index_code'] == 'ZB_0130' or j['index_code'] == 'ZB_0117' or j['index_code'] == 'ZB_0311' or j['index_code'] == 'ZB_0313' or j['index_code'] == 'ZB_0307':
                                tmpEnt.update({j['index_code']: float(j['index_value']) })
            ent_list.append(tmpEnt)
        return ent_list

    def __queryReportT002DataByYear__(self,entEnterprise, regionsData):
        ent_list = []
        year = calendar.datetime.date.today().year
        start_year = year - 4;
        if entEnterprise['platform_region_code'] is not None:
            regionsInfo = regionsData.regionData(entEnterprise['platform_region_code'])

            # 纳入失信被执行人
            sqlRegion = """
                    SELECT year,index_value FROM region_index WHERE index_code = 'QY030402' AND region_code = %s
                """
            list1 = self.localDb.Query(sqlRegion,entEnterprise['platform_region_code'])

            # 非标区域
            sqlRegionNon = """
                    SELECT credit_code from base_non_standard_default WHERE province_code in %s or city_code IN %s or county_code IN %s 
                """
            # dataCenter = CcxDataCenter(logSql=True, autocommit=True)
            region_codes = []
            region_codes.append(entEnterprise['platform_region_code'])
            data1 = self.dataCenterDb.Query(sqlRegionNon,(region_codes,region_codes,region_codes ))

            if len(data1) > 0:
                entEnterprise['nonStandardRegion'] = 'TRUE'
            else:
                entEnterprise['nonStandardRegion'] = 'FALSE'

            sqlRegionAcc = """
                    SELECT  creditcode  from fc_accbilloddetails WHERE province_code in %s or city_code IN %s or county_code IN %s 
                """
            data2 = self.localDb.Query(sqlRegionAcc,(region_codes,region_codes,region_codes ))
            if len(data2) > 0:
                entEnterprise['accbilloddetailRegion'] = 'TRUE'
            else:
                entEnterprise['accbilloddetailRegion'] = 'FALSE'

            for x in regionsInfo:
                if x['year'] != None and x['value'] != None:
                    entEnterprise.update({x['target_code'] + "_" + x['year']: float(x['value'])})

            for x in list1:
                if x['year'] != None and x['index_value'] != None:
                    entEnterprise.update({'QY030402' + "_" + str(x['year']): float(x['index_value'])})
        ent_list.append(entEnterprise)
        return ent_list

    # 纳入失信被执行人
    def __queryTCR0033_V2__(self, comp_codes):
        sql = """
                SELECT itcode as comp_code from TCR0033_V2 WHERE itcode in %s group by itcode
            """
        fcdb = FCDB(logSql=False, autocommit=True,bDict=False)
        data = fcdb.Query(sql,(comp_codes, ))
        return data

    # 非标违约
    def __queryBaseNonStandard_V2__(self, credit_codes):
        sql = """
                SELECT credit_code  as credit_code from base_non_standard_default WHERE credit_code in %s group by credit_code
            """
        dataCenter = CcxDataCenter(logSql=True, autocommit=True)
        data = dataCenter.Query(sql,(credit_codes, ))
        return data

    # 非标违约--区域
    def __queryBaseNonStandardForRegions_V2__(self, region_codes):
        sql = """
                SELECT credit_code from base_non_standard_default WHERE province_code in %s or city_code IN %s or county_code IN %s group by credit_code
            """
        dataCenter = CcxDataCenter(logSql=True, autocommit=True)
        data = dataCenter.Query(sql,(region_codes,region_codes,region_codes ))
        return data

    # 债券余额
    def __queryDebentureBase_V2__(self):
        sql = """
            WITH secodes  AS (
            SELECT issuer_code, max(secode) AS secode FROM deb_debenture_base
            where remaining_sum IS NOT NULL
            AND issuer_code <> ''
            GROUP BY issuer_code, security_id 
            )
            SELECT c.issuer_code, SUM(remaining_sum)
            FROM deb_debenture_base d
            INNER JOIN secodes c ON d.secode = c.secode
            GROUP BY c.issuer_code
            """
        localDb = LocalDb(logSql=False, autocommit=True,bDict=False)
        data = localDb.Query(sql)
        return data

        # 债券违约
    def __queryDfltinfo_V2__(self, comp_codes):
        sql = """
            SELECT compcode  as comp_code from tq_bd_dfltinfo WHERE compcode in %s group by compcode
            """
        localDb = LocalDb(logSql=True, autocommit=True,bDict=False)
        data = localDb.Query(sql,(comp_codes, ))
        return data

    # 是否上市
    def __queryListed_V2__(self, credit_codes):
        sql = """
            SELECT credit_code as credit_code FROM enterprise_info WHERE sector IN ('港股','主板') and credit_code in %s group by credit_code
            """
        dataCenter = CcxDataCenter(logSql=True, autocommit=True)
        data = dataCenter.Query(sql,(credit_codes, ))
        return data

    # QE下调
    def __queryQeChange_V2__(self, credit_codes):
        sql = """
            SELECT credit_code from rating_change WHERE credit_code in %s and change_type = '下调' and rating_org = 'QE评级' group by credit_code
            """
        localDb = LocalDb(logSql=False, autocommit=True,bDict=False)
        data = localDb.Query(sql,(credit_codes, ))
        return data

    # 外评下调
    def __queryExternalChange_V2__(self, credit_codes):
        sql = """
            SELECT credit_code   from rating_change WHERE credit_code in %s and change_type = '下调' and rating_org not in('QE评级','惠誉','标准普尔','穆迪') group by credit_code
            """
        localDb = LocalDb(logSql=False, autocommit=True,bDict=False)
        data = localDb.Query(sql,(credit_codes, ))
        return data

    # 商票逾期
    def __queryAccbilloddetail_V2__(self, credit_codes):
        sql = """
            SELECT creditcode from fc_accbilloddetails WHERE creditcode in %s  order by creditcode
            """
        localDb = LocalDb(logSql=False, autocommit=True,bDict=False)
        data = localDb.Query(sql,(credit_codes, ))
        return data

    # 商票逾期  区域
    def __queryAccbilloddetailForRegions_V2__(self, region_codes):
        sql = """
                SELECT  creditcode  from fc_accbilloddetails WHERE province_code in %s or city_code IN %s or county_code IN %s group by creditcode
            """
        localDb = LocalDb(logSql=False, autocommit=True,bDict=False)
        data = localDb.Query(sql,(region_codes,region_codes,region_codes))
        return data

    # 入口
    def getEnterpriseList(self) -> List[EntEnterprise]:

        # 抽取最新名单
        self.logger.info("加载 财汇主体名单")
        remoteDatas = self.__queryRemote__()
        remoteDatas = {d["COMPCODE"]: d for d in remoteDatas}
        self.logger.info("加载 财汇主体名单：%d" % len(remoteDatas))

        # 抽取本地名单
        self.logger.info("加载 本地主体名单")
        localDatas = self.__queryLocal__()
        localDatas = {d["comp_code"]: d for d in localDatas}
        self.logger.info("加载 本地主体名单：%d" % len(localDatas))

        now = datetime.datetime.now()

        # 检查新增数据
        newCompKeys = remoteDatas.keys() - localDatas.keys()
        insertDatas = []
        # 提取新增主体
        for k, v in remoteDatas.items():
            if newCompKeys.__contains__(k):
                ent = EntEnterprise()
                ent.comp_code = v["COMPCODE"]
                ent.credit_code = v["OUTCODE"]
                ent.enterprise_name = v["COMPNAME"]
                ent.update_time = v["DATA_DOWNLOAD_TIME"]
                ent.classify_type = 0
                ent.create_time = now
                insertDatas.append(ent)
        self.logger.info("新增主体：%d" % len(insertDatas))

        # 提取更新主体
        sameCodes = list(remoteDatas.keys() & localDatas.keys())


        updateDatas = []
        for code in sameCodes:
            # 查询财汇数据
            remoteData = remoteDatas.get(code)
            d = localDatas[code]
            ent = EntEnterprise()
            ent.id = d["id"]
            ent.comp_code = remoteData["COMPCODE"]
            ent.credit_code = remoteData["OUTCODE"]
            ent.enterprise_name = remoteData["COMPNAME"]
            ent.update_time = remoteData["DATA_DOWNLOAD_TIME"]
            ent.create_time = d["create_time"]
            ent.classify_type = 0
            updateDatas.append(ent)
        self.logger.info("更新主体：%d" % len(updateDatas))
        del remoteDatas
        del newCompKeys
        del localDatas
        del sameCodes
        # 合并数据并返回
        retList = []
        retList.extend(insertDatas)
        retList.extend(updateDatas)

        return retList
